package com.creditease.mobile.news.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import com.creditease.mobile.news.model.ImgModel;
import com.creditease.mobile.news.model.NewsModel;
import com.creditease.mobile.rss.RssConst;
import com.creditease.mobile.util.FileUtil;
import com.creditease.mobile.vo.NewsDetail;

import java.io.File;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class NewsSQLiteOpenHelper extends SQLiteOpenHelper {
    public static final String ARTICLE_READ_AUTO_ID = "article_read_auto_id";
    public static final String ARTICLE_READ_COL_ID = "article_read_col_id";
    public static final String ARTICLE_READ_DOC_ID = "article_read_doc_id";
    public static final String ARTICLE_READ_LIST_TABLE = "article_read_status_list";
    public static final String ARTICLE_READ_STATUS = "article_read_is_read";
    public static final String CID = "cid";
    public static final String CITY_AUTO_ID = "city_auto_id";
    public static final String CITY_NAME = "city_name";
    public static final String CITY_PINYIN = "city_pinyin";
    public static final String CITY_PROVINCE = "city_province";
    public static final String CITY_SELECT_COUNT = "city_select_count";
    public static final String CITY_TABLE = "city_list";
    public static final String COLLECT_AUTOID = "auto_id";
    public static final String COLLECT_CREATE_AT = "create_at";
    public static final String COLLECT_IMAGE_LOCAL = "image_loacl";
    public static final String COLLECT_ISCHECKED = "ischecked";
    public static final String COLLECT_OBJID = "collect_id";
    public static final String COLLECT_PATH = "collect_path";
    public static final String COLLECT_TABLE = "collect_table";
    public static final String COLLECT_TITLE = "collect_title";
    public static final String COLLECT_TYPE = "collect_type";
    public static final String COMMENTS_AUTHOR = "comments_author";
    public static final String COMMENTS_AUTO_ID = "comments_auto_id";
    public static final String COMMENTS_COL_ID = "comments_col_id";
    public static final String COMMENTS_DOC_ID = "comments_doc_id";
    public static final String COMMENTS_FLOOR = "comments_floor";
    public static final String COMMENTS_IS_READ = "comments_is_read";
    public static final String COMMENTS_LIST_TABLE = "comments_list";
    public static final String COMMENTS_POSTID = "comments_postid";
    public static final String COMMENTS_PTIME = "comments_ptime";
    public static final String COMMENTS_REPLY_BOARD = "comments_reply_board";
    public static final String COMMENTS_REPLY_COUNT = "comments_reply_count";
    public static final String COMMENTS_TIE = "comments_tie";
    public static final String COMMENTS_TITLE = "comments_title";
    private static final String DATABASE_NAME = "netease_news.db";
    private static final int DATABASE_VERSION = 73;
    public static final String DEFAULT_SORT_ORDER = "id DESC";
    public static final String MORE_COLUMN_TABLE = "more_columns";
    public static final String MYID = "myid";
    public static final String NEWS_AUTO_ID = "news_auto_id";
    public static final String NEWS_COL_ID = "news_col_id";
    public static final String NEWS_DIGEST = "news_digest";
    public static final String NEWS_DOC_ID = "news_doc_id";
    public static final String NEWS_EXTENDS_JSON = "news_extends_json";
    public static final String NEWS_IMG_SRC = "news_img_src";
    public static final String NEWS_IS_FIRST = "news_is_first";
    public static final String NEWS_IS_HASIMG = "news_is_hasimg";
    public static final String NEWS_IS_READ = "news_is_read";
    public static final String NEWS_LIST_TABLE = "news_list";
    public static final String NEWS_PTIME = "news_ptime";
    public static final String NEWS_REPLY_COUNT = "news_reply_count";
    public static final String NEWS_SPECIAL_ID = "news_special_id";
    public static final String NEWS_SPECIAL_INDEX = "news_index";
    public static final String NEWS_SPECIAL_TNAME = "news_special_tname";
    public static final String NEWS_SUBTITLE = "news_subtitle";
    public static final String NEWS_TITLE = "news_title";
    public static final String NEWS_URL = "news_url";
    public static final String OAUTH_AUTO_ID = "oauth_auto_id";
    public static final String OAUTH_NAME = "oauth_name";
    public static final String OAUTH_TABLE = "oauth_list";
    public static final String OAUTH_TOKEN = "oauth_token";
    public static final String OAUTH_TOKEN_SECRET = "oauth_token_secret";
    public static final String OAUTH_TYPE = "oauth_type";
    public static final String PHOTO_AUTO_ID = "photo_auto_id";
    public static final String PHOTO_CLIENTCOVER = "photo_clientcover";
    public static final String PHOTO_DATE = "photo_date";
    public static final String PHOTO_IMGSUM = "photo_imgsum";
    public static final String PHOTO_LIST_TABLE = "photo_list";
    public static final String PHOTO_SETID = "photo_setid";
    public static final String PHOTO_SETNAME = "photo_setname";
    public static final String PIC_AUTO_ID = "pic_auto_id";
    public static final String PIC_JSON = "pic_json";
    public static final String PIC_SETID = "pic_setid";
    public static final String PIC_TABLE = "pic";
    private static final String TAG = NewsSQLiteOpenHelper.class.getName();
    public static final String TID = "tid";
    public static final String TNAME = "tname";
    public static final String TOP_COLUMN_TABLE = "top_columns";
    public static final String VOTE_AUTO_ID = "auto_id";
    public static final String VOTE_ID = "vote_id";
    public static final String VOTE_TABLE = "vote_table";

    public NewsSQLiteOpenHelper(Context paramContext) {
        super(paramContext, "rss_news.db", null, 73);
    }

    public void onCreate(SQLiteDatabase paramSQLiteDatabase) {
        paramSQLiteDatabase
                .execSQL("CREATE TABLE top_columns (myid INTEGER PRIMARY KEY AUTOINCREMENT,tname NTEXT,tid NTEXT,cid NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE more_columns (myid INTEGER PRIMARY KEY AUTOINCREMENT,tname NTEXT,tid NTEXT,cid NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE vote_table (auto_id INTEGER PRIMARY KEY AUTOINCREMENT,vote_id NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE collect_table (auto_id INTEGER PRIMARY KEY AUTOINCREMENT,collect_path NTEXT,collect_id NTEXT,collect_title NTEXT,collect_type NTEXT,create_at NTEXT,ischecked NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE news_list (news_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,news_col_id NTEXT,news_doc_id NTEXT,news_img_src NTEXT,news_digest NTEXT,news_reply_count NTEXT,news_url NTEXT,news_title NTEXT,news_ptime NTEXT,news_subtitle NTEXT,news_is_first NTEXT,news_is_read NTEXT,news_is_hasimg NTEXT,news_special_id NTEXT,news_index NTEXT,news_special_tname NTEXT,news_extends_json NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE comments_list (comments_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,comments_col_id NTEXT,comments_doc_id NTEXT,comments_reply_count NTEXT,comments_title NTEXT,comments_tie NTEXT,comments_author NTEXT,comments_reply_board NTEXT,comments_postid NTEXT,comments_floor NTEXT,comments_is_read NTEXT,comments_ptime NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE photo_list (photo_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,photo_setid INTEGER,photo_clientcover NTEXT,photo_setname NTEXT,photo_imgsum NTEXT,photo_date NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE pic (pic_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,pic_setid NTEXT,pic_json NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE article_read_status_list (article_read_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,article_read_col_id NTEXT,article_read_doc_id NTEXT,article_read_is_read NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE oauth_list (oauth_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,oauth_type NTEXT,oauth_token NTEXT,oauth_token_secret NTEXT,oauth_name NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE city_list (city_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,city_name NTEXT,city_province NTEXT,city_pinyin NTEXT,city_select_count INTEGER);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE news (news_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,news_col_id NTEXT,news_doc_id NTEXT,body NTEXT,title NTEXT,hasNext NTEXT,ptime NTEXT,source NTEXT);");
        paramSQLiteDatabase
                .execSQL("CREATE TABLE img (news_auto_id INTEGER PRIMARY KEY AUTOINCREMENT,news_col_id NTEXT,news_doc_id NTEXT,url NTEXT,path NTEXT);");
    }

    public void onUpgrade(SQLiteDatabase paramSQLiteDatabase, int paramInt1,
                          int paramInt2) {
        Log.w("newsDB", "Upgrading database from version " + paramInt1 + " to "
                + paramInt2 + ", which will destroy all old data");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS top_columns");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS more_columns");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS vote_table");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS collect_table");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS news_list");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS comments_list");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS photo_list");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS pic");
        paramSQLiteDatabase
                .execSQL("DROP TABLE IF EXISTS article_read_status_list");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS oauth_list");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS city_list");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS news");
        paramSQLiteDatabase.execSQL("DROP TABLE IF EXISTS img");

        onCreate(paramSQLiteDatabase);
    }

    public void updateNewsDB(NewsDetail model) {

        SQLiteDatabase db = getWritableDatabase();
        try {
            ContentValues cv = new ContentValues();
            cv.put("body", model.body);
            cv.put("hasNext", model.hasNext);
            db.update("news", cv, "news_auto_id=?", new String[]{model.id});
        } finally {
            db.close();
        }
    }

    public synchronized void insertDataToNewsListTable(String paramString1,
                                                       String paramString2, String paramString3, String paramString4,
                                                       String paramString5, String paramString6, String paramString7,
                                                       String paramString8, String paramString9, String paramString10,
                                                       String paramString11, String paramString12, String paramString13,
                                                       String paramString14, String paramString15, String paramString16) {
        ContentValues localContentValues = new ContentValues();
        localContentValues.put("news_col_id", paramString1);
        localContentValues.put("news_doc_id", paramString2);
        localContentValues.put("news_img_src", paramString3);
        localContentValues.put("news_digest", paramString4);
        localContentValues.put("news_reply_count", paramString5);
        localContentValues.put("news_url", paramString6);
        localContentValues.put("news_title", paramString7);
        localContentValues.put("news_ptime", paramString8);
        localContentValues.put("news_subtitle", paramString9);
        localContentValues.put("news_is_first", paramString10);
        localContentValues.put("news_is_read", paramString11);
        localContentValues.put("news_is_hasimg", paramString12);
        if ((paramString13 == null) || ("".equalsIgnoreCase(paramString13)))
            paramString13 = "";
        localContentValues.put("news_special_id", paramString13);
        if ((paramString14 == null) || ("".equalsIgnoreCase(paramString14)))
            paramString14 = "";
        localContentValues.put("news_index", paramString14);
        if ((paramString15 == null) || ("".equalsIgnoreCase(paramString15)))
            paramString15 = "";
        localContentValues.put("news_special_tname", paramString15);
        localContentValues.put("news_extends_json", paramString16);
        SQLiteDatabase dbDatabase = getWritableDatabase();
        dbDatabase.insert("news_list", null, localContentValues);
        dbDatabase.close();
    }

    public void insertDataToNewsTable(String paramString1, String paramString2,
                                      String paramString3, String paramString4, String paramString5,
                                      String paramString6, String paramString7) {
        ContentValues localContentValues = new ContentValues();
        localContentValues.put("news_col_id", paramString1);
        localContentValues.put("news_doc_id", paramString2);
        localContentValues.put("body", paramString3);
        localContentValues.put("title", paramString4);
        localContentValues.put("hasNext", paramString5);
        localContentValues.put("ptime", paramString6);

        localContentValues.put("source", paramString7);

        SQLiteDatabase dbDatabase = getWritableDatabase();
        dbDatabase.insert("news", null, localContentValues);
        dbDatabase.close();
    }

    public void deleteDataToNewsTabel(String docId) {
        SQLiteDatabase dbDatabase = getWritableDatabase();
        dbDatabase.delete("news", "news_doc_id='" + docId + "'", null);
    }

    public void deleteDataToNewsListTabel(String colId) {
        SQLiteDatabase dbDatabase = getWritableDatabase();
        dbDatabase.delete("news_list", "news_col_id='" + colId + "'", null);
    }

    public void deleteDataToImgTabel(String docId) {
        SQLiteDatabase dbDatabase = getWritableDatabase();
        dbDatabase.delete("img", "news_doc_id='" + docId + "'", null);
    }

    public void insertDataToImgTable(String news_col_id, String news_doc_id,
                                     String url, String path) {
        ContentValues localContentValues = new ContentValues();
        localContentValues.put("news_col_id", news_col_id);
        localContentValues.put("news_doc_id", news_doc_id);
        localContentValues.put("url", url);
        localContentValues.put("path", path);

        SQLiteDatabase dbDatabase = getWritableDatabase();
        dbDatabase.insert("img", null, localContentValues);
        dbDatabase.close();
    }

    public synchronized List<ImgModel> getImgs(String colId) {

        SQLiteDatabase db = getWritableDatabase();
        List<ImgModel> result = new ArrayList<ImgModel>();
        String sql = "select news_auto_id,url,path,news_col_id,news_doc_id from img where news_col_id=? ";
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, new String[]{colId});
            while (cursor.moveToNext()) {
                String id = cursor.getString(0);
                String url = cursor.getString(1);
                String filePath = cursor.getString(2);
                String cid = cursor.getString(3);
                String dcoId = cursor.getString(4);
                ImgModel model = new ImgModel(id, url, filePath, cid, dcoId);
                result.add(model);
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return result;
    }

    public  List<NewsModel> getDataFromNewsListTable(String cid) {
        SQLiteDatabase db = getWritableDatabase();

        List<NewsModel> result = new ArrayList<NewsModel>();
        String sql = "select * from news_list where news_col_id=? ";
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, new String[]{cid});
            while (cursor.moveToNext()) {

                String id = cursor.getString(0);
                // String cid = cursor.getString(1);
                String docid = cursor.getString(2);
                String imgsrc = cursor.getString(3);
                String digest = cursor.getString(4);
                String replyCount = cursor.getString(5);
                String url = cursor.getString(6);
                String title = cursor.getString(7);
                String ptime = cursor.getString(8);
                String subtitle = cursor.getString(9);

                NewsModel model = new NewsModel(id, cid, "", replyCount, "", digest, url, docid, title, "", "", "", subtitle,
                        imgsrc, ptime);

                result.add(model);
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return result;
    }

    public synchronized void deleImg(String cid) {
        Log.d(TAG, "删除图片开始：" + cid);
        List<NewsModel> list2 = getDataFromNewsListTable(cid);
        Set<String> newIds = new HashSet<String>();
        for (NewsModel news : list2) {
            newIds.add(news.docid);
        }
        //删除图片
        List<ImgModel> urls = getImgs(cid);
        Log.d(TAG, cid + ",图片数：" + urls.size());
        int count = 0;
        for (ImgModel img : urls) {
            String docid = img.newsId;
            if (!newIds.contains(docid)) {
                String newFileName = FileUtil.getFileName(img.url);
                String path = RssConst.NEWS_IMG_CACHE + newFileName;
                File file = new File(path);
                if (file.exists()) {
                    file.delete();
                }
                count++;
                getWritableDatabase().delete("img","news_auto_id=?",new String[]{img.id});
            }
        }
        Log.d(TAG, cid + ",删除图片数：" + count);
        Log.d(TAG, "删除图片结束" + cid);
    }
}